
 SELECT --fat.sta_idsys,vsec.SCR_RIFERIMENTO  , vsec.tmov_des,fat.dst_des,vsec.par_id,fat.REGIVAPRO_LABEL,
   case when fat.SOG_PARIVA is null then fat.SOG_CODFIS else fat.SOG_PARIVA end "CDF/PARIVA",
   --vsc.SOG_ID SOGGETTO_UE,
   vsec.SOG_RAGSOC "RAGIONE_SOCIALE",
   'x' nome,
   'x' E_mail,
   'UMBRIA ENERGY' "CODICE FONTE",
  
  CASE WHEN  fat.REGIVAPRO_LABEL
  LIKE '%GN%' OR vsec.SCR_RIFERIMENTO  LIKE '%GN%'  THEN 'GAS' ELSE
      CASE WHEN fat.REGIVAPRO_LABEL  LIKE '%UEV%' OR vsec.SCR_RIFERIMENTO  LIKE '%UEV%' THEN 'ELETTRICITA' END END "CODICE CATEGORIA",
   
   CONCAT (FAT.FAT_ANNO,FAT.FAT_NUM) "IDENTIFICATIVO FATTURA",
   trunc(FAT.FAT_DT) "DATA EMISSIONE FATTURA", 
   FAP.PFAT_TOT "IMPORTO FATTURA",
   
      vsec.PAR_ID "N.RO PARITA",   

         trunc (vsec.MOV_DT) "DATA MOVIMENTO",   
         trunc(vsec.rata_dtsca) "DATA SCADENZA RATA", 
         trunc(vsec.inc_dtpag) "DATA INCASSO RATA",
        
         case when vsec.rata_num is null or vsec.rata_num = 0 then vsec.SCR_IMP_D 
                   else vsec.rata_imp end "IMPORTO DARE",   

         vsec.SCR_IMP_A "IMPORTO AVERE",  
         
         
         CASE WHEN vsec.rata_num = 0 THEN '' ELSE TO_CHAR(vsec.rata_num) END RATA_NUM,

        ' ' "DOCUMENTO",
      case when vsec.SCR_RIFERIMENTO like '%Incass%' then vsec.SCR_RIFERIMENTO else
         case when vsec.MOV_NOTA like '%INCASSO%'  then vsec.MOV_NOTA else 
           case when 
                 vsec.SCR_RIFERIMENTO  like '%Fattur%' then vsec.SCR_RIFERIMENTO  else vsec.MOV_NOTA 
           end 
           end 
      end "DOC.DI RIFERIMENTO",


           CASE WHEN vsec.TMOV_DES LIKE 'INCASSO%' THEN vsec.tmov_des ELSE  case when vsec.tmov_id in (1,3,4) then vsec.TSCR_DES  else vsec.tmov_des end END "DESCR. MOVIMENTO",

         CASE WHEN vsec.ISS_ID_PARTITA =602 THEN 'APERTA' ELSE 'CHIUSA' END STATO_PARTITA,
         vsec.ocr,
        (select distinct max (concat(concat(ubi.con_anno,'/'),ubi.con_id)) from  fatturas  fas,conubifrn ubi  
        where fas.az_id='01' and ubi.az_id = '01' and fat.tfat_id=fas.tfat_id and ubi.cuf_id=fas.cuf_id) "Codice Contratto",
        fat.ragf_id  "Gruppo fatturazione",
       rf.ragf_dessta  "Descrizione Gruppo Fatt."



    FROM  
    
asm_e_c_simplicitta vsec,v_FATTURAT FAT,v_ragfat rf,
(SELECT tfat_id,sum(pfat_tot) pfat_tot FROM FATTURAP WHERE pfat_flpiegen = 1 and az_id = '01' group by tfat_id) FAP

   WHERE 

          
            
         fat.az_id = '01'
       
           and fat.mov_num(+) = vsec.mov_num
         AND FAT.PAR_ID(+) = vsec.par_id
           AND  FAP.TFAT_ID(+) =FAT.TFAT_ID
          -- and fat.sta_idsys(+) <> 1
          -- and vsec.par_id = 1416918 
           --and vsec.mov_num = 106732--1343315 --174115
      -- and vsec.par_id in (173586)
/*175324,
174009,
175597,
174429,
175487,
175422,
174115,
175401,
175395,
175634,
175571,
175573,
173585,
174452,
175660,
174509,
174270,
175454,
175456,
175458,
175460,
173907,
175508,
175509,
175523,
174136,
173824,
174080,
173721,
1191683
)
  */         and fat.ragf_id=rf.ragf_id
             and rf.az_id='01'
             and rf.ver_dtfine = to_date('31129999','ddmmyyyy')
          -- and FAT.FAT_ANNO=2007 and FAT.FAT_NUM=137 
ORDER BY        vsec.SOG_ID,
                vsec.PAR_ID,
                vsec.MOV_DT
